﻿namespace Data
{
    using Common;
    using System;
    using System.Collections;
    using System.Data;
    using System.Data.SqlClient;
    using System.Configuration;
    using System.Linq;
    using System.Xml.Linq;

    /// <summary>
    ///IUser 的摘要说明
    /// </summary>
    public class IUser
    {
        string host = @"data source = GERRY\SQLEXPRESS;User ID = sa; Password = 12341234; database = IUser; Connection Reset = false;";
        public IUser()
        {
        }
        public int Insert(User user)
        {
            int c1, c2;
            //create connection
            SqlConnection thisConnection = new SqlConnection(host);
            thisConnection.Open();
            //create data adapter
            SqlDataAdapter thisAdapter = new SqlDataAdapter("select * from Users", thisConnection);
            //create command builder
            SqlCommandBuilder thisbuilder = new SqlCommandBuilder(thisAdapter);
            //create dataset
            DataSet thisDataSet = new DataSet();
            //fill thisdataset
            thisAdapter.Fill(thisDataSet, "Users");
            c1 = thisDataSet.Tables["Users"].Rows.Count;
            //new row
            DataRow thisRow = thisDataSet.Tables["Users"].NewRow();
            thisRow["UserId"] = user.UserId;
            thisRow["Name"] = user.Name;
            thisRow["Code"] = user.Code;
            thisRow["Sex"] = user.Sex;
            thisRow["Email"] = user.Email;
            thisRow["CodeSecurityQuestion"] = user.CodeSecurityQuestion;
            thisRow["CodeSecurityAnswer"] = user.CodeSecurityAnswer;
            thisDataSet.Tables["Users"].Rows.Add(thisRow);
            c2 = thisDataSet.Tables["Users"].Rows.Count;
            //update sql
            thisAdapter.Update(thisDataSet, "Users");
            //close the connection
            thisConnection.Close();
            if (c2 - c1 == 1) return 1;
            else return 0;
        }
        public bool Delete(string userid)
        {
            //create connection
            SqlConnection thisConnection = new SqlConnection(host);
            thisConnection.Open();
            //create command
            SqlCommand thisCommand = thisConnection.CreateCommand();
            thisCommand.CommandText = "delete from Users where ( UserId = '" + userid + "');";
            int judge = thisCommand.ExecuteNonQuery();
            thisConnection.Close();
            if (judge > 0) return true;
            else return false;
        }
        public bool Update(User user)
        {
            bool j1 = this.Delete(user.UserId);
            int j2 = this.Insert(user);
            if ( j1 == true && j2 >= 0 ) return true;
            else return false;
        }
        public ArrayList GetAllUsers()//从零开始的数组
        {
            ArrayList userlist = new ArrayList();
            User user = new User();
            //create connection
            SqlConnection thisConnection = new SqlConnection(host);
            thisConnection.Open();
            //create command
            SqlCommand thisCommand = thisConnection.CreateCommand();
            thisCommand.CommandText = "select * from users";
            SqlDataReader thisReader = thisCommand.ExecuteReader();
            while (thisReader.Read())
            {
                user.UserId = Convert.ToString(thisReader["UserId"]);
                user.Name = Convert.ToString(thisReader["Name"]);
                user.Code = Convert.ToString(thisReader["Code"]);
                user.Email = Convert.ToString(thisReader["Email"]);
                user.Sex = Convert.ToString(thisReader["Sex"]);
                user.CodeSecurityQuestion = Convert.ToString(thisReader["codesecurityquestion"]);
                user.CodeSecurityAnswer = Convert.ToString(thisReader["CodeSecurityAnswer"]);
                userlist.Add(user);
            }
            thisReader.Close();
            thisConnection.Close();
            return userlist;
        }
        public User GetUserByUserId(string userid)
        {
            User user = new User();
            //create connection
            SqlConnection thisConnection = new SqlConnection(host);
            thisConnection.Open();
            //create command
            SqlCommand thisCommand = thisConnection.CreateCommand();
            thisCommand.CommandText = "select * from users where ( UserID = '" + userid + "');";
            SqlDataReader thisReader = thisCommand.ExecuteReader();
            if (thisReader.Read())
            {
                user.UserId = Convert.ToString(thisReader["UserId"]);
                user.Name = Convert.ToString(thisReader["Name"]);
                user.Code = Convert.ToString(thisReader["Code"]);
                user.Email = Convert.ToString(thisReader["Email"]);
                user.Sex = Convert.ToString(thisReader["Sex"]);
                user.CodeSecurityQuestion = Convert.ToString(thisReader["codesecurityquestion"]);
                user.CodeSecurityAnswer = Convert.ToString(thisReader["CodeSecurityAnswer"]);
                thisReader.Close();
                thisConnection.Close();
                return user;
            }
            else
            {
                thisReader.Close();
                thisConnection.Close();
                return null;
            }
        }
        public ArrayList GetUserByUserName(string name)
        {
            ArrayList userlist = new ArrayList();
            User user = new User();
            //create connection
            SqlConnection thisConnection = new SqlConnection(host);
            thisConnection.Open();
            //create command
            SqlCommand thisCommand = thisConnection.CreateCommand();
            thisCommand.CommandText = "select * from users where ( name = '" + name + "')";
            SqlDataReader thisReader = thisCommand.ExecuteReader();
            while (thisReader.Read())
            {
                user.UserId = Convert.ToString(thisReader["UserId"]);
                user.Name = Convert.ToString(thisReader["Name"]);
                user.Code = Convert.ToString(thisReader["Code"]);
                user.Email = Convert.ToString(thisReader["Email"]);
                user.Sex = Convert.ToString(thisReader["Sex"]);
                user.CodeSecurityQuestion = Convert.ToString(thisReader["codesecurityquestion"]);
                user.CodeSecurityAnswer = Convert.ToString(thisReader["CodeSecurityAnswer"]);
                userlist.Add(user);
            }
            thisReader.Close();
            thisConnection.Close();
            return userlist;
        }
    }
}
